import openpyxl
from openpyxl.styles import Font, Border, Side, Alignment
from openpyxl.utils import get_column_letter
from datetime import datetime


def _thin_border() -> Border:
    side = Side(style="thin")
    return Border(left=side, right=side, top=side, bottom=side)


def _apply_range_border(sheet, min_row: int, max_row: int, min_col: int, max_col: int) -> None:
    border = _thin_border()
    for row in sheet.iter_rows(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col):
        for cell in row:
            cell.border = border


def build_bank_reconciliation_content(sheet, top_row: int, left_col: int, report_data: dict,
                                      apply_dimensions: bool = True) -> int:
    """
    在工作表上以相对位置(top_row, left_col)绘制“银行存款余额调节表”的内容与样式，返回结束行号。
    - 列范围为 8 列（A..H 相对偏移）
    - 行范围为 10 行（1..10 相对偏移）
    - 当 apply_dimensions=True 时，按模板精确设置列宽与行高
    """
    s = top_row
    c0 = left_col

    # 样式（黑体；仅标题加粗）
    font_title = Font(name="黑体", size=18, bold=True)
    font_meta = Font(name="黑体", size=10)
    font_th = Font(name="黑体", size=10)
    font_td = Font(name="黑体", size=10)

    align_center = Alignment(horizontal="center", vertical="center", wrap_text=True)
    align_left = Alignment(horizontal="left", vertical="center", wrap_text=True)
    align_right = Alignment(horizontal="right", vertical="center", wrap_text=True)

    # 列宽（单位：字符）与行高（单位：磅），基于用户提供的模板尺寸
    col_widths = [44.94, 8.11, 13.58, 9.11, 27.19, 8.11, 13.58, 9.11]
    row_heights = [33, 25, 25, 25, 25, 25, 25, 25, 13.5, 13.5]

    if apply_dimensions:
        for i, w in enumerate(col_widths):
            col_letter = get_column_letter(c0 + i)
            sheet.column_dimensions[col_letter].width = w
        for i, h in enumerate(row_heights):
            sheet.row_dimensions[s + i].height = h

    # 顶部：标题（第1行）合并8列
    sheet.merge_cells(start_row=s, start_column=c0, end_row=s, end_column=c0 + 7)
    cell_title = sheet.cell(s, c0, "银行存款余额调节表")
    cell_title.font = font_title
    cell_title.alignment = align_center

    # 顶部信息（第2、3行）
    company_name = report_data.get("company_name", "")
    account_number = report_data.get("account_number", "")
    bank_name = report_data.get("bank_name", "")
    today_str = datetime.now().strftime("%Y年%m月%d日")

    # 第2行：编制单位 | 编制日期
    sheet.merge_cells(start_row=s + 1, start_column=c0, end_row=s + 1, end_column=c0 + 3)
    sheet.merge_cells(start_row=s + 1, start_column=c0 + 4, end_row=s + 1, end_column=c0 + 7)
    cell_unit = sheet.cell(s + 1, c0, f"编制单位：{company_name}")
    cell_unit.font = font_meta
    cell_unit.alignment = align_left
    cell_date = sheet.cell(s + 1, c0 + 4, f"编制日期：{today_str}")
    cell_date.font = font_meta
    cell_date.alignment = align_left

    # 第3行：开户行 | 账号 | 单位：元
    sheet.merge_cells(start_row=s + 2, start_column=c0, end_row=s + 2, end_column=c0 + 3)
    sheet.merge_cells(start_row=s + 2, start_column=c0 + 4, end_row=s + 2, end_column=c0 + 6)
    cell_bank = sheet.cell(s + 2, c0, f"开户行：{bank_name}")
    cell_bank.font = font_meta
    cell_bank.alignment = align_left
    cell_acc = sheet.cell(s + 2, c0 + 4, f"账号：{account_number}")
    cell_acc.font = font_meta
    cell_acc.alignment = align_left
    cell_currency = sheet.cell(s + 2, c0 + 7, "单位：元")
    cell_currency.font = font_meta
    cell_currency.alignment = align_left

    # 表头（第4行，8列）
    headers = ("项目", "日期", "金额", "款项备注", "项目", "日期", "金额", "款项备注")
    for i, text in enumerate(headers):
        h = sheet.cell(s + 3, c0 + i, text)
        h.font = font_th
        h.alignment = align_center

    # 内容（第5-8行）
    # 获取当前月日
    current_month_day = datetime.now().strftime("%m月%d日")
    
    # 左边项目
    sheet.cell(s + 4, c0, "企业银行存款日记账余额").font = font_td
    sheet.cell(s + 4, c0 + 1, current_month_day).font = font_td
    sheet.cell(s + 5, c0, "加：银行已收，企业未收款").font = font_td
    sheet.cell(s + 6, c0, "减：银行已付，企业未付款").font = font_td
    sheet.cell(s + 7, c0, "调节后的存款余额").font = font_td

    # 右边项目
    sheet.cell(s + 4, c0 + 4, "银行对账单余额").font = font_td
    sheet.cell(s + 4, c0 + 5, current_month_day).font = font_td
    sheet.cell(s + 5, c0 + 4, "加：企业已收，银行未收款").font = font_td
    sheet.cell(s + 6, c0 + 4, "减：企业已付，银行未付款").font = font_td
    sheet.cell(s + 7, c0 + 4, "调节后的存款余额").font = font_td

    # 金额格式
    money_fmt = "#,##0.00"
    
    # 设置加减项的金额字段（第5、6行）- 初始值为0
    # 左边加减项
    cell_add_left = sheet.cell(s + 5, c0 + 2, 0)
    cell_add_left.number_format = money_fmt
    cell_add_left.font = font_td
    
    cell_sub_left = sheet.cell(s + 6, c0 + 2, 0)
    cell_sub_left.number_format = money_fmt
    cell_sub_left.font = font_td
    
    # 右边加减项
    cell_add_right = sheet.cell(s + 5, c0 + 6, 0)
    cell_add_right.number_format = money_fmt
    cell_add_right.font = font_td
    
    cell_sub_right = sheet.cell(s + 6, c0 + 6, 0)
    cell_sub_right.number_format = money_fmt
    cell_sub_right.font = font_td

    # 设置余额金额字段
    balance_value = float(report_data.get("balance", 0.0))
    
    # 设置余额金额字段（第4行）
    cell_balance_left = sheet.cell(s + 4, c0 + 2, balance_value)
    cell_balance_left.number_format = money_fmt
    cell_balance_left.font = font_td
    
    cell_balance_right = sheet.cell(s + 4, c0 + 6, balance_value)
    cell_balance_right.number_format = money_fmt
    cell_balance_right.font = font_td

    # 合计公式（第7行的金额列）
    cell_left_total = sheet.cell(s + 7, c0 + 2, f"={get_column_letter(c0 + 2)}{s + 4}+{get_column_letter(c0 + 2)}{s + 5}-{get_column_letter(c0 + 2)}{s + 6}")
    cell_left_total.number_format = money_fmt
    cell_left_total.font = font_td
    cell_left_total.alignment = align_right

    cell_right_total = sheet.cell(s + 7, c0 + 6, f"={get_column_letter(c0 + 6)}{s + 4}+{get_column_letter(c0 + 6)}{s + 5}-{get_column_letter(c0 + 6)}{s + 6}")
    cell_right_total.number_format = money_fmt
    cell_right_total.font = font_td
    cell_right_total.alignment = align_right

    # 对齐：日期列居中、金额列右对齐，其余左对齐（覆盖第5-8行）
    for r in range(s + 4, s + 7 + 1):
        sheet.cell(r, c0 + 1).alignment = align_center
        sheet.cell(r, c0 + 5).alignment = align_center
        sheet.cell(r, c0 + 2).alignment = align_right
        sheet.cell(r, c0 + 6).alignment = align_right
        sheet.cell(r, c0 + 0).alignment = align_left
        sheet.cell(r, c0 + 3).alignment = align_left
        sheet.cell(r, c0 + 4).alignment = align_left
        sheet.cell(r, c0 + 7).alignment = align_left

    # 边框：第4-8行、8列
    _apply_range_border(sheet, s + 3, s + 7, c0, c0 + 7)

    # 底部签字（第10行）
    sheet.merge_cells(start_row=s + 9, start_column=c0 + 0, end_row=s + 9, end_column=c0 + 2)
    sheet.merge_cells(start_row=s + 9, start_column=c0 + 3, end_row=s + 9, end_column=c0 + 5)
    sheet.merge_cells(start_row=s + 9, start_column=c0 + 6, end_row=s + 9, end_column=c0 + 7)
    sheet.cell(s + 9, c0 + 0, "财务负责人：").font = font_meta
    sheet.cell(s + 9, c0 + 0).alignment = align_left
    sheet.cell(s + 9, c0 + 3, "会计：").font = font_meta
    sheet.cell(s + 9, c0 + 3).alignment = align_left
    sheet.cell(s + 9, c0 + 6, "出纳：").font = font_meta
    sheet.cell(s + 9, c0 + 6).alignment = align_left

    return s + 9


def create_report_from_code(sheet, start_row: int, report_data: dict):
    """
    兼容旧接口：从第 start_row 行、A 列开始绘制调节表内容（会设置列宽/行高）。
    """
    build_bank_reconciliation_content(sheet, start_row, 1, report_data, apply_dimensions=True)
    print("调节表（八列单表样式，黑体版）已生成。")


def draw_bank_reconciliation_block(sheet, start_row: int, report_data: dict) -> int:
    """
    仅负责在既有工作表上绘制一块“银行存款余额调节表”，不创建/保存工作簿。
    返回绘制结束的最后一行行号（便于后续追加）。
    """
    return build_bank_reconciliation_content(sheet, start_row, 1, report_data, apply_dimensions=True)


def create_workbook_with_reconciliation(report_data: dict):
    """
    创建一个全新的工作簿，并在首个工作表第1行绘制调节表，返回(workbook, sheet, end_row)。
    """
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "融大"
    end_row = build_bank_reconciliation_content(ws, 1, 1, report_data, apply_dimensions=True)
    return wb, ws, end_row


def save_new_reconciliation_report(output_path: str, report_data: dict) -> None:
    """
    将调节表绘制到一个全新工作簿，并保存为指定路径。
    """
    wb, _, _ = create_workbook_with_reconciliation(report_data)
    wb.save(output_path)


def append_reconciliation_to_file(file_path: str, sheet_name: str, report_data: dict, spacing_rows: int = 2) -> None:
    """
    向已有Excel文件的指定工作表追加一个新的调节表块。
    按银行账号分列存储，不同账号间用2列空白隔离，同一账号不同日期用4行空白隔离。
    """
    try:
        wb = openpyxl.load_workbook(file_path)
    except FileNotFoundError:
        save_new_reconciliation_report(file_path, report_data)
        return

    if sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
    else:
        ws = wb.create_sheet(title=sheet_name)

    # 获取当前日期
    current_date = datetime.now().strftime("%Y-%m-%d")
    
    # 查找目标银行账号的列位置
    target_account = report_data.get("account_number", "")
    target_col = _find_account_column(ws, target_account)
    
    if target_col is None:
        # 新账号，在最后一列后添加（间隔2列）
        last_col = _find_last_account_column(ws)
        target_col = last_col + 3  # 间隔2列空白
    
    # 在目标列中查找最后一个对账内容的位置
    start_row = _find_last_reconciliation_row(ws, target_col)
    
    # 构建对账内容
    build_bank_reconciliation_content(ws, start_row, target_col, report_data, apply_dimensions=True)
    
    wb.save(file_path)


def _get_cell_value_safe(ws, row: int, col: int) -> str:
    """
    安全地获取单元格值，处理合并单元格的情况
    """
    try:
        cell = ws.cell(row=row, column=col)
        if hasattr(cell, 'value'):
            return str(cell.value) if cell.value is not None else ""
        return ""
    except:
        return ""

def _find_account_column(ws, account_number: str) -> int:
    """
    在sheet中查找指定银行账号的列位置
    返回列号（从1开始），如果没找到返回None
    """
    # 查找包含账号信息的列
    for col in range(1, ws.max_column + 1):
        # 检查第3行（开户行和账号行）
        cell_value = _get_cell_value_safe(ws, 3, col)
        if cell_value and str(account_number) in cell_value:
            return col
        
        # 检查第4行（表头行）
        cell_value = _get_cell_value_safe(ws, 4, col)
        if cell_value and str(account_number) in cell_value:
            return col
            
        # 检查第5行（余额行）
        cell_value = _get_cell_value_safe(ws, 5, col)
        if cell_value and str(account_number) in cell_value:
            return col
    
    return None


def _find_last_account_column(ws) -> int:
    """
    查找最后一个银行账号的列位置
    返回列号（从1开始），如果没有账号则返回0
    查找逻辑：找到最右边一列包含"单位：元"和"款项备注"的列
    """
    last_col = 0
    for col in range(1, ws.max_column + 1):
        # 检查第3行是否有"单位：元"
        unit_cell = _get_cell_value_safe(ws, 3, col)
        # 检查第4行是否有"款项备注"
        remark_cell = _get_cell_value_safe(ws, 4, col)
        
        if unit_cell and "单位：元" in unit_cell and remark_cell and "款项备注" in remark_cell:
            last_col = col
    
    return last_col


def _find_initial_write_position(ws, target_col: int) -> int:
    """
    查找初始写入位置
    查找逻辑：在目标列中查找包含"银行存款余额调节表"的行，从该行开始写入
    """
    for row in range(1, ws.max_row + 1):
        cell_value = _get_cell_value_safe(ws, row, target_col)
        if cell_value and "银行存款余额调节表" in cell_value:
            return row
    
    # 如果没有找到，从第1行开始
    return 1

def _find_last_reconciliation_row(ws, target_col: int) -> int:
    """
    在指定列中查找最后一个对账内容的行位置
    返回下一个对账内容应该开始的行号
    查找逻辑：从最下面开始查找包含"财务负责人："、"会计："和"出纳："的行
    """
    # 从最下面开始向上查找，找到最后一个包含签字信息的行
    last_row = 0
    
    for row in range(ws.max_row, 0, -1):
        cell_value = _get_cell_value_safe(ws, row, target_col)
        # 检查是否包含签字信息
        if cell_value and (
            "财务负责人：" in cell_value or
            "会计：" in cell_value or
            "出纳：" in cell_value
        ):
            last_row = row
            break
    
    # 如果找到了内容，返回最后一个内容行 + 4行空白 + 1
    if last_row > 0:
        return last_row + 5
    else:
        # 如果没有找到内容，查找初始写入位置
        return _find_initial_write_position(ws, target_col)